"""
Copyright (c) 2022 Huawei Technologies Co.,Ltd.

openGauss is licensed under Mulan PSL v2.
You can use this software according to the terms and conditions of the Mulan PSL v2.
You may obtain a copy of Mulan PSL v2 at:

          http://license.coscl.org.cn/MulanPSL2

THIS SOFTWARE IS PROVIDED ON AN "AS IS" BASIS, WITHOUT WARRANTIES OF ANY KIND,
EITHER EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO NON-INFRINGEMENT,
MERCHANTABILITY OR FIT FOR A PARTICULAR PURPOSE.
See the Mulan PSL v2 for more details.
"""
"""
Case Type   : JDBC+mysql和opengauss兼容B库对比
Case Name   : JDBC默认参数+TINYINT(n)无效取值范围测试
Create At   : 2023/10/16
Owner       : opentestcase022
Description :
    1.读取通用tinyint SQL脚本
    2.复制jdbc到当前目录
    3.生成java文件,通过JDBC连接数据库,执行sql
    4.编译上一步生成的java文件，生成可执行文件
    5.执行上一步生成的可执行文件，对mysql和opengauss兼容B库结果进行对比
Expect      :
    1.执行成功
    2.执行成功
    3.执行成功
    4.执行成功
    5.执行成功，结果一致
History     :
"""
import os
import unittest

from yat.test import Node

from testcase.CONNECTORS.JDBC.CompatibilityB.DATATYPE. \
    Opengauss_Function_Connect_JDBC_Common import ComJDBC
from testcase.utils.Logger import Logger


class MysqlTest2(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        text = f'-----{os.path.basename(__file__)} start-----'
        self.log.info(text)
        self.comjdbc = ComJDBC()
        self.mysql_node = Node('mysql')
        self.og_node = Node('PrimaryDbBmode')
        self.mysql_info = {'f_name': 'mysql_jdbc_datatype2',
                           'db_type': 'mysql',
                           'db_host': self.mysql_node.db_host,
                           'db_port': self.mysql_node.db_port,
                           'db_name': self.mysql_node.db_name,
                           'db_user': self.mysql_node.db_user,
                           'db_passwd': self.mysql_node.db_password,
                           'db_driver': 'com.mysql.jdbc.Driver',
                           'sql': ''}
        self.og_info = {'f_name': 'opengauss_jdbc_datatype2',
                        'db_type': 'opengauss',
                        'db_host': self.og_node.db_host,
                        'db_port': self.og_node.db_port,
                        'db_name': self.og_node.db_name,
                        'db_user': self.og_node.db_user,
                        'db_passwd': self.og_node.db_password,
                        'db_driver': 'org.opengauss.Driver',
                        'sql': ''}
        self.sql_path = os.path.join(
            'testcase', 'script', 'CompatibilityB', 'DATATYPE', 'TINYINT',
            'CompatibilityB_DataType_Tinyint_Case0002.sql')
        self.expect = "*** mysql\n" \
                      "--- openGauss\n" \
                      "***************\n" \
                      "*** 7,27 ****\n" \
                      "create view v_tinyint0002 as " \
                      "select * from t_tinyint0002\n" \
                      "SQL SUCCESS\n" \
                      "insert t_tinyint0002 values(-129, -129, -129, -129)\n" \
                      "! ERROR:Data truncation: Out of range value for " \
                      "column 'c1' at row 1\n" \
                      "insert t_tinyint0002 values(128, 128, 128, 128)\n" \
                      "! ERROR:Data truncation: Out of range value for " \
                      "column 'c1' at row 1\n" \
                      "insert t_tinyint0002 values('@#$%^&*!', '@#$%^&*!', " \
                      "'@#$%^&*!', '@#$%^&*!')\n" \
                      "! ERROR:Incorrect integer value: '@#$%^&*!' for " \
                      "column 'c1' at row 1\n" \
                      "insert t_tinyint0002 values('熊猫是国宝', '熊猫是国宝'," \
                      " '熊猫是国宝', '熊猫是国宝')\n" \
                      "! ERROR:Incorrect integer value: '熊猫是国宝' for " \
                      "column 'c1' at row 1\n" \
                      "insert t_tinyint0002 values('111', '111', '111', " \
                      "'111')\n" \
                      "SQL SUCCESS\n" \
                      "insert t_tinyint0002 values('\"111\"', '\"111\"', " \
                      "'\"111\"', '\"111\"')\n" \
                      "! ERROR:Incorrect integer value: '\"111\"' for " \
                      "column 'c1' at row 1\n" \
                      "insert t_tinyint0002 values('()[]{}', '()[]{}', " \
                      "'()[]{}', '()[]{}')\n" \
                      "! ERROR:Incorrect integer value: '()[]{}' for " \
                      "column 'c1' at row 1\n" \
                      "insert t_tinyint0002 values('ياخشىمۇسىز', " \
                      "'ياخشىمۇسىز', 'ياخشىمۇسىز', 'ياخشىمۇسىز')\n" \
                      "! ERROR:Incorrect integer value: 'ياخشىمۇسىز' for " \
                      "column 'c1' at row 1\n" \
                      "select * from t_tinyint0002\n" \
                      "111|111|111|111\n" \
                      "1rows\n" \
                      "--- 7,39 ----\n" \
                      "create view v_tinyint0002 as " \
                      "select * from t_tinyint0002\n" \
                      "SQL SUCCESS\n" \
                      "insert t_tinyint0002 values(-129, -129, -129, -129)\n" \
                      "! ERROR:[7.212.122.13:41838/7.220.76.148:51041] " \
                      "ERROR: tinyint out of range\n" \
                      "!   Where: referenced column: c1\n" \
                      "insert t_tinyint0002 values(128, 128, 128, 128)\n" \
                      "! ERROR:[7.212.122.13:41838/7.220.76.148:51041] " \
                      "ERROR: tinyint out of range\n" \
                      "!   Where: referenced column: c1\n" \
                      "insert t_tinyint0002 values('@#$%^&*!', '@#$%^&*!', " \
                      "'@#$%^&*!', '@#$%^&*!')\n" \
                      "! ERROR:[7.212.122.13:41838/7.220.76.148:51041] " \
                      "ERROR: invalid input syntax for type tinyint:" \
                      " \"@#$%^&*!\"\n" \
                      "!   Position: 29\n" \
                      "!   Where: referenced column: c1\n" \
                      "insert t_tinyint0002 values('熊猫是国宝', " \
                      "'熊猫是国宝', '熊猫是国宝', '熊猫是国宝')\n" \
                      "! ERROR:[7.212.122.13:41838/7.220.76.148:51041] " \
                      "ERROR: invalid input syntax for type tinyint:" \
                      " \"熊猫是国宝\"\n" \
                      "!   Position: 29\n" \
                      "!   Where: referenced column: c1\n" \
                      "insert t_tinyint0002 values('111', '111', " \
                      "'111', '111')\n" \
                      "SQL SUCCESS\n" \
                      "insert t_tinyint0002 values('\"111\"', '\"111\"', " \
                      "'\"111\"', '\"111\"')\n" \
                      "! ERROR:[7.212.122.13:41838/7.220.76.148:51041] " \
                      "ERROR: invalid input syntax for type tinyint: " \
                      "\"\"111\"\"\n" \
                      "!   Position: 29\n" \
                      "!   Where: referenced column: c1\n" \
                      "insert t_tinyint0002 values('()[]{}', '()[]{}', " \
                      "'()[]{}', '()[]{}')\n" \
                      "! ERROR:[7.212.122.13:41838/7.220.76.148:51041] " \
                      "ERROR: invalid input syntax for type tinyint:" \
                      " \"()[]{}\"\n" \
                      "!   Position: 29\n" \
                      "!   Where: referenced column: c1\n" \
                      "insert t_tinyint0002 values('ياخشىمۇسىز'" \
                      ", 'ياخشىمۇسىز'" \
                      ", 'ياخشىمۇسىز'" \
                      ", 'ياخشىمۇسىز')\n" \
                      "! ERROR:[7.212.122.13:41838/7.220.76.148:51041] " \
                      "ERROR: invalid input syntax for type tinyint: " \
                      "\"ياخشىمۇسىز\"\n" \
                      "!   Position: 29\n" \
                      "!   Where: referenced column: c1\n" \
                      "select * from t_tinyint0002\n" \
                      "111|111|111|111\n" \
                      "1rows\n"

    def test_1(self):
        text = 'step1:读取tinyint SQL脚本; expect:执行成功'
        self.log.info(f'-----{text}-----')
        self.log.info(self.sql_path)
        sql = self.comjdbc.read_sql_file(self.sql_path)
        self.assertNotEqual(len(sql.strip()), 0, f'执行失败：{text}')
        f_sql = self.comjdbc.format_case_sql(sql)
        self.mysql_info['sql'] = f_sql
        self.og_info['sql'] = f_sql

        text = 'step2:复制jdbc到当前目录; expect:执行成功'
        self.log.info(f'-----{text}-----')
        res = self.comjdbc.get_jdbc(self.mysql_info['db_type'])
        self.assertEqual(len(res.strip()), 0, f'mysql执行失败：{text}')
        res = self.comjdbc.get_jdbc(self.og_info['db_type'])
        self.assertEqual(len(res.strip()), 0, f'opengauss执行失败：{text}')

        text = 'step3:生成java文件,通过JDBC连接数据库,执行sql; expect:执行成功'
        self.log.info(f'-----{text}-----')
        self.comjdbc.write_java_file(**self.mysql_info)
        self.comjdbc.write_java_file(**self.og_info)

        text = 'step4:编译上一步生成的java文件，生成可执行文件; expect:执行成功'
        self.log.info(f'-----{text}-----')
        res = self.comjdbc.compile_java_file(self.mysql_info['db_type'])
        self.assertEqual(len(res.strip()), 0, f'mysql执行失败：{text}')
        res = self.comjdbc.compile_java_file(self.og_info['db_type'])
        self.assertEqual(len(res.strip()), 0, f'opengauss执行失败：{text}')

        text = 'step5:执行上一步生成的可执行文件，对mysql和opengauss兼容B库结果进行对比; ' \
               'expect:执行成功，结果一致'
        self.log.info(f'-----{text}-----')
        mysql_res, _ = self.comjdbc.run_java_file(self.mysql_info['db_type'])
        og_res, _ = self.comjdbc.run_java_file(self.og_info['db_type'])

        self.log.info('-----对比mysql和opengauss兼容B库执行结果-----')
        diff = self.comjdbc.compare_sql_res(mysql_res, og_res, 'mysql',
                                            'openGauss')
        self.log.info('-----对比预期结果与实际运行结果-----')
        diff_diff = self.comjdbc.compare_sql_err_res(self.expect, diff,
                                                     '预期结果', '实际结果')
        self.assertEqual(len(diff_diff.strip()), 0, '预期结果与实际运行结果不一致')

    def tearDown(self):
        self.log.info('----run teardown----')
        self.log.info('-----清理环境-----')
        self.comjdbc.remove_java_file()
        self.log.info(f'-----{os.path.basename(__file__)} end-----')
